package com.dotflat.satistics.repository.crm.impl;

import com.dotflat.satistics.repository.crm.ContractProductRepository;
import com.dotflat.satistics.vo.home.ProductVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * *******************************
 * Created by Ron.
 * form com.matt
 * on 2018/7/20
 * *******************************
 */
@Repository
public class ContractProductRepositoryImpl implements ContractProductRepository {

    @Autowired
    private JdbcTemplate crmJdbcTemplate;

    @Override
    public List<ProductVo> findAllVisible() {
        List<ProductVo> list = crmJdbcTemplate.query("select id, name from product where isVisible= 1", new BeanPropertyRowMapper<ProductVo>(ProductVo.class));
        return list;
    }

    @Override
    public List<Map<String, Object>> findUnitPriceByAreaIdAndProductIdGroupByCreated(Date begin, Date end, String areaId, String productId) {
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList("select DATE(c.paidDate) as created, sum(cp.practicalTotalAmount) as totalAmount, sum(cp.count) as count from contract c , contract_product cp " +
                "where c.id = cp.contractId AND c.paidDate >= ? and  c.paidDate <= ? AND   c.areaId=? AND cp.productId = ? AND c.payStatus=1 GROUP BY date(c.paidDate) ", new Object[]{begin, end, areaId, productId});
        return list;
    }

    @Override
    public List<Map<String, Object>> findUnitPriceByAreaId(String areaId, Date begin, Date end) {
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList("select DATE(c.paidDate) as created, sum(cp.practicalTotalAmount) as totalAmount, sum(cp.count) as count from contract c , contract_product cp " +
                "where c.id = cp.contractId AND c.paidDate >= ? and  c.paidDate <= ? AND   c.areaId=? AND c.payStatus=1 GROUP BY date(c.paidDate) ", new Object[]{begin, end, areaId});
        return list;
    }

    @Override
    public List<Map<String, Object>> findUnitPriceByAreaIdInAndProductIdGroupByAreaId(Date begin, Date end, String areaIds, String productId) {
        String sql = String.format("select c.areaId as areaId, sum(cp.practicalTotalAmount) as totalAmount, sum(cp.count) as count , date(c.paidDate) as created from contract c , contract_product cp " +
                "where c.id = cp.contractId AND c.paidDate >= ? and  c.paidDate <= ? AND   c.areaId IN (%s) AND cp.productId = ? AND c.payStatus=1 GROUP BY c.areaId ", areaIds);
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList(sql, new Object[]{begin, end,  productId});
        return list;
    }
}
